-- Start of DDL Script for Package Body PEMEX2.PKG_PREDIO_AFECTADO
-- Generated 24/04/2012 22:18:54 from PEMEX2@XE

CREATE OR REPLACE 
PACKAGE pkg_predio_afectado
  IS  
  procedure getpredioafectadoCriterios(
            pvalor_busqueda         IN VARCHAR2,
            pcriterio               IN VARCHAR2,
            pcve_localidad          IN VARCHAR2,
             resultado out sys_refcursor
    );
  PROCEDURE setPredioAfectado
     ( pNOMBRE IN VARCHAR2,
       pAPELLIDO_PATERNO IN VARCHAR2,
       pAPELLIDO_MATERNO IN VARCHAR2,
       pSEXO IN VARCHAR2,
       pRFC IN VARCHAR2,
       pCVE_ELECTOR IN VARCHAR2,
       pCVE_LOCALIDAD IN VARCHAR2,
       pSUPERFICIE IN VARCHAR2,
       pNO_PREDIO IN VARCHAR2,
       pDENOMINADO IN VARCHAR2,
       pNORTE IN VARCHAR2,
       pSUR IN VARCHAR2,
       pESTE IN VARCHAR2,
       pOESTE IN VARCHAR2,
       pACREDITADO IN VARCHAR2,
       pACREDITADO_NO IN VARCHAR2,
       pACREDITADO_FECHA IN VARCHAR2,
       pNO_NOTARIO IN VARCHAR2,
       pNO_ESCRITURA IN VARCHAR2,
       pNO_VOLUMEN IN VARCHAR2,
       pNO_NOTARIA IN VARCHAR2,
       pTIPO_ACREDITACION IN VARCHAR2,
       preferencia          IN VARCHAR2,
       PCVE_AFECTADO IN VARCHAR2,
       PCVE_PREDIO  IN VARCHAR2,
        pmovimiento  IN VARCHAR2
       
     );
  PROCEDURE getpredioafectado (
      pcvepredio     IN       VARCHAR2,
      pcveafectado   IN       VARCHAR2,
      resultado      OUT      sys_refcursor
   );
  PROCEDURE EliminarPredio (pcve_predio IN Varchar2);
END; -- Package spec
/


CREATE OR REPLACE 
PACKAGE BODY pkg_predio_afectado
IS
   PROCEDURE EliminarPredio (pcve_predio IN Varchar2)
   IS
      clave      NUMBER;
      cve_obra   NUMBER;
   BEGIN
      DELETE CAT_PREDIO
      WHERE  cve_predio = pcve_predio;
         
      DELETE CAT_PERSONAS WHERE
      CVE_AFECTADO IN ( 
                        select CVE_AFECTADO FROM CAT_PREDIO WHERE  cve_predio = pcve_predio
                        );
   END;
   
   PROCEDURE setpredioafectado (
      pnombre              IN   VARCHAR2,
      papellido_paterno    IN   VARCHAR2,
      papellido_materno    IN   VARCHAR2,
      psexo                IN   VARCHAR2,
      prfc                 IN   VARCHAR2,
      pcve_elector         IN   VARCHAR2,
      pcve_localidad       IN   VARCHAR2,
      psuperficie          IN   VARCHAR2,
      pno_predio           IN   VARCHAR2,
      pdenominado          IN   VARCHAR2,
      pnorte               IN   VARCHAR2,
      psur                 IN   VARCHAR2,
      peste                IN   VARCHAR2,
      poeste               IN   VARCHAR2,
      pacreditado          IN   VARCHAR2,
      pacreditado_no       IN   VARCHAR2,
      pacreditado_fecha    IN   VARCHAR2,
      pno_notario          IN   VARCHAR2,
      pno_escritura        IN   VARCHAR2,
      pno_volumen          IN   VARCHAR2,
      pno_notaria          IN   VARCHAR2,
      ptipo_acreditacion   IN   VARCHAR2,
      preferencia          IN   VARCHAR2,
      pcve_afectado        IN   VARCHAR2,
      pcve_predio          IN   VARCHAR2,
      pmovimiento          IN   VARCHAR2
   )
   IS
      clave_personas         NUMBER;
      clave_predio           NUMBER;
      v_envio_acreditacion   DATE          := NULL;
      estado_actualizacion   NUMBER;
      v_no_predio            VARCHAR (200) := '';
   BEGIN
      IF (LENGTH (TRIM (pacreditado_fecha)) > 0)
      THEN
         v_envio_acreditacion := TO_DATE (pacreditado_fecha, 'dd/mm/yyyy');
      END IF;

      IF (pmovimiento = 'nuevo')
      THEN
         SELECT seq_cat_personas.NEXTVAL
           INTO clave_personas
           FROM DUAL;

         INSERT INTO cat_personas
                     (cve_afectado, nombre, apellido_p,
                      apellido_m, sexo, rfc, clave_elector
                     )
              VALUES (clave_personas, pnombre, papellido_paterno,
                      papellido_materno, psexo, prfc, pcve_elector
                     );

         SELECT seq_cat_predio.NEXTVAL
           INTO clave_predio
           FROM DUAL;

         IF (pno_predio IS NULL OR LENGTH (pno_predio) = 0)
         THEN
            v_no_predio := clave_predio || '_TEMPORAL';
         ELSE
            v_no_predio := pno_predio;
         END IF;

         INSERT INTO cat_predio
                     (cve_predio, cve_localidad, superficie, no_predio,
                      denominado, norte, sur, este, oeste, acreditado,
                      acreditado_no, acreditado_fecha, no_notario,
                      no_escritura, volumen, no_notaria,
                      cve_afectado, cve_tipo_acreditacion, referencia
                     )
              VALUES (clave_predio, pcve_localidad, psuperficie, v_no_predio,
                      pdenominado, pnorte, psur, peste, poeste, pacreditado,
                      pacreditado_no, v_envio_acreditacion, pno_notario,
                      pno_escritura, pno_volumen, pno_notaria,
                      clave_personas, ptipo_acreditacion, preferencia
                     );
      ELSE
         IF (pmovimiento = 'editar')
         THEN
            UPDATE cat_personas
               SET nombre = pnombre,
                   apellido_p = papellido_paterno,
                   apellido_m = papellido_materno,
                   sexo = psexo,
                   rfc = prfc,
                   clave_elector = pcve_elector
             WHERE cve_afectado = pcve_afectado;

            SELECT COUNT (*)
              INTO estado_actualizacion
              FROM cat_predio
             WHERE cve_afectado = pcve_afectado;

            IF (pcve_predio IS NULL)
            THEN
               --select seq_cat_predio.NEXTVAL into CLAVE_PREDIO from dual;
               INSERT INTO cat_predio
                           (cve_predio, cve_localidad,
                            superficie, no_predio, denominado, norte,
                            sur, este, oeste, acreditado,
                            acreditado_no, acreditado_fecha,
                            no_notario, no_escritura, volumen,
                            no_notaria, cve_afectado, cve_tipo_acreditacion,
                            referencia
                           )
                    VALUES (seq_cat_predio.NEXTVAL, pcve_localidad,
                            psuperficie, pno_predio, pdenominado, pnorte,
                            psur, peste, poeste, pacreditado,
                            pacreditado_no, v_envio_acreditacion,
                            pno_notario, pno_escritura, pno_volumen,
                            pno_notaria, pcve_afectado, ptipo_acreditacion,
                            preferencia
                           );
            ELSE
               UPDATE cat_predio
                  SET cve_localidad = pcve_localidad,
                      superficie = psuperficie,
                      no_predio = pno_predio,
                      denominado = pdenominado,
                      norte = pnorte,
                      sur = psur,
                      este = peste,
                      oeste = poeste,
                      acreditado = pacreditado,
                      acreditado_no = pacreditado_no,
                      acreditado_fecha = v_envio_acreditacion,
                      no_notario = pno_notario,
                      no_escritura = pno_escritura,
                      volumen = pno_volumen,
                      no_notaria = pno_notaria,
                      cve_afectado = pcve_afectado,
                      cve_tipo_acreditacion = ptipo_acreditacion,
                      referencia = preferencia
                WHERE cve_predio = pcve_predio;
            END IF;
         END IF;
      END IF;
   END;

   PROCEDURE getpredioafectadocriterios (
      pvalor_busqueda   IN       VARCHAR2,
      pcriterio         IN       VARCHAR2,
      pcve_localidad    IN       VARCHAR2,
      resultado         OUT      sys_refcursor
   )
   IS
      sqltemp   VARCHAR (4000);
   BEGIN
      sqltemp :=
         ' select cp.rfc,nvl(cpre.no_predio,'' '') no_predio,
                        '' <strong> Nombre: </strong>'' || cp.nombre ||'' ''||cp.apellido_p||'' ''||cp.apellido_m ||''.  <strong> Colindancias N: </strong>''||
                        cpre.norte||'' <strong> S: </strong> ''||cpre.sur||'' <strong> E: </strong>''||cpre.este||'' <strong> O: </strong>''||
                        cpre.oeste||'' <strong> No. Escritura: </strong>''||cpre.NO_ESCRITURA nombre_completo,
                        cpre.superficie,
                        cpre.denominado,
                        (select count(*) from solicitud_afectado where  cve_predio=cpre.cve_predio)  as veces_afectado,
                        (select count(*) from solicitud_afectado where  cve_predio=cpre.cve_predio and fecha_envio_permiso_paso is not null)  as veces_tramitado,
                        cpre.norte ||'' ''||cpre.sur||'' ''||cpre.este||'' ''||cpre.oeste as colindancias
                        ,''Localidad: ''||loc.descripcion ||'' Municipio: ''||mun.descripcion  ubicacion,
                        cp.cve_afectado,
                        cp.nombre || '' ''|| cp.apellido_p||'' ''||cp.apellido_m nombre_afectado,
                        cp.rfc,
                        cp.clave_elector,
                        cp.sexo,
                        cpre.cve_predio,
                        cpre.CVE_LOCALIDAD,
                        mun.cve_municipio,
                        cpre.SUPERFICIE,
                        cpre.NO_PREDIO,DENOMINADO,
                        cpre.NORTE,
                        cpre.SUR,
                        cpre.ESTE,
                        cpre.OESTE,
                        cpre.ACREDITADO,
                        cpre.ACREDITADO_NO,
                        to_char(cpre.ACREDITADO_FECHA,''dd/mm/yyyy'') AS FECHA,
                        cpre.NO_NOTARIO,
                        cpre.NO_ESCRITURA,
                        cpre.VOLUMEN,
                        cpre.NO_NOTARIA,
                        cpre.CVE_TIPO_ACREDITACION,
                        cpre.REFERENCIA
                    from cat_predio cpre , cat_personas cp
                        ,cat_localidades loc, cat_municipio mun
                    where
                        cp.cve_afectado         = cpre.cve_afectado(+)
                        and cpre.cve_localidad  = loc.cve_localidad(+)
                        and loc.cve_municipio   = mun.cve_municipio(+) ';

      IF (pvalor_busqueda IS NOT NULL) AND (pvalor_busqueda = '1')
      THEN
         sqltemp :=
               sqltemp
            || ' and HOMOTEXT(cpre.no_predio) like HOMOTEXT(REPLACE (''%'
            || pcriterio
            || '%'', '' '', ''%''))';
      END IF;

      IF (pvalor_busqueda IS NOT NULL) AND (pvalor_busqueda = '2')
      THEN
         sqltemp :=
               sqltemp
            || ' and HOMOTEXT(cp.nombre||'' ''||cp.apellido_p||'' ''||cp.apellido_m) like HOMOTEXT (REPLACE (''%'
            || pcriterio
            || '%'', '' '', ''%''))';
      END IF;

      IF (pvalor_busqueda IS NOT NULL) AND (pvalor_busqueda = '3')
      THEN
         sqltemp :=
               sqltemp
            || ' and HOMOTEXT(cpre.denominado) like HOMOTEXT(REPLACE (''%'
            || pcriterio
            || '%'', '' '', ''%''))';
      END IF;

      IF (pvalor_busqueda IS NOT NULL) AND (pvalor_busqueda = '4')
      THEN
         sqltemp :=
               sqltemp
            || ' and HOMOTEXT(cpre.norte ||'' ''||cpre.sur||'' ''||cpre.este||'' ''||cpre.oeste) like HOMOTEXT(REPLACE (''%'
            || pcriterio
            || '%'', '' '', ''%''))';
      END IF;

      IF (pcve_localidad IS NOT NULL)
      THEN
         sqltemp := sqltemp || ' and cpre.cve_localidad = ' || pcve_localidad;
      END IF;

      sqltemp := sqltemp || ' ORDER BY cpre.cve_predio DESC ';

      OPEN resultado FOR sqltemp;
   END;

   PROCEDURE getpredioafectado (
      pcvepredio     IN       VARCHAR2,
      pcveafectado   IN       VARCHAR2,
      resultado      OUT      sys_refcursor
   )
   IS
      sqltemp   VARCHAR (4000);
   BEGIN
      sqltemp :=
         '  select cpre.cve_predio, cpre.superficie,cpre.no_predio,cpre.denominado,cpre.norte,cpre.sur,cpre.este,
            cpre.oeste,cpre.acreditado,cpre.acreditado_no,TO_CHAR(cpre.acreditado_fecha,''dd/mm/rrrr'') acreditado_fecha,cpre.no_notario,cpre.no_escritura,
            cpre.volumen,cpre.no_notaria,cpre.cve_tipo_acreditacion,cpre.referencia,
            cp.nombre,cp.apellido_p,cp.apellido_m,cp.sexo,cp.rfc,cp.clave_elector,cp.cve_afectado,
            loc.cve_localidad,loc.descripcion,
            mun.cve_municipio,mun.descripcion
            from cat_predio cpre , cat_personas cp
                        ,cat_localidades loc, cat_municipio mun
                    where
                        cp.cve_afectado         = cpre.cve_afectado(+)
                        and cpre.cve_localidad  = loc.cve_localidad(+)
                        and loc.cve_municipio   = mun.cve_municipio(+) ';

      IF (pcvepredio IS NOT NULL)
      THEN
         sqltemp := sqltemp || ' and cpre.cve_predio = ' || pcvepredio;
      END IF;

      IF (pcveafectado IS NOT NULL)
      THEN
         sqltemp := sqltemp || ' and cp.cve_afectado = ' || pcveafectado;
      END IF;
      
      sqltemp := sqltemp || ' ORDER BY cpre.cve_predio DESC ';

      OPEN resultado FOR sqltemp;
   END;
END;
/


-- End of DDL Script for Package Body PEMEX2.PKG_PREDIO_AFECTADO
